- frmBusCardHolder_StaffRecord.vb
- project /
1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmBusCardHolder_StaffRecord
4
5 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
6 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
7 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
8 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
9 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
10 End If
11 Dim b As Brush = SystemBrushes.ControlText
12 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
13
14 End Sub
15
16 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
17 Try
18 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
19 If lblSet.Text = "Bus Holder Entry" Then
20 Me.Hide()
21 frmBusCardHolder_Staff.Show()
22 frmBusCardHolder_Staff.txtID.Text = dr.Cells(0).Value.ToString()
23 frmBusCardHolder_Staff.txtS_ID.Text = dr.Cells(1).Value.ToString()
24 frmBusCardHolder_Staff.txtStaffID.Text = dr.Cells(2).Value.ToString()
25 frmBusCardHolder_Staff.txtStaffName.Text = dr.Cells(3).Value.ToString()
26 frmBusCardHolder_Staff.txtSchoolName.Text = dr.Cells(4).Value.ToString()
27 frmBusCardHolder_Staff.cmbBusNo.Text = dr.Cells(5).Value.ToString()
28 frmBusCardHolder_Staff.cmbLocationName.Text = dr.Cells(6).Value.ToString()
29 frmBusCardHolder_Staff.dtpJoiningDate.Text = dr.Cells(7).Value.ToString()
30 frmBusCardHolder_Staff.cmbStatus.Text = dr.Cells(8).Value.ToString()
31 frmBusCardHolder_Staff.btnDelete.Enabled = True
32 frmBusCardHolder_Staff.btnUpdate.Enabled = True
33 frmBusCardHolder_Staff.btnSave.Enabled = False
34 End If
35 If lblSet.Text = "Bus Fee Payment" Then
36 Me.Hide()
37 frmBusFeePayment_Staff.Show()
38 frmBusFeePayment_Staff.txtBusHolderID.Text = dr.Cells(0).Value.ToString()
39 frmBusFeePayment_Staff.txtSt_ID.Text = dr.Cells(1).Value.ToString()
40 frmBusFeePayment_Staff.txtStaffID.Text = dr.Cells(2).Value.ToString()
41 frmBusFeePayment_Staff.txtStaffName.Text = dr.Cells(3).Value.ToString()
42 frmBusFeePayment_Staff.txtLocation.Text = dr.Cells(6).Value.ToString()
43 frmBusFeePayment_Staff.fillInstallment()
44 con = New SqlConnection(cs)
45 con.Open()
46 cmd = con.CreateCommand()
47 cmd.CommandText = "SELECT Designation FROM Staff where ST_ID=@d1"
48 cmd.Parameters.AddWithValue("@d1", dr.Cells(0).Value)
49 rdr = cmd.ExecuteReader()
50 If rdr.Read() Then
51 frmBusFeePayment_Staff.txtDesignation.Text = rdr.GetValue(0)
52 End If
53 If (rdr IsNot Nothing) Then
54 rdr.Close()
55 End If
56 If con.State = ConnectionState.Open Then
57 con.Close()
58 End If
59 End If
60 Catch ex As Exception
61 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
62 End Try
63 End Sub
64
65 Public Sub GetData()
66 Try
67 con = New SqlConnection(cs)
68 con.Open()
69 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Staff.BCH_ID) as [ID],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID], RTRIM(StaffName) as [StaffName],RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Staff.Status) as [Status] from Staff,BusCardHolder_Staff,Location,BusInfo,schoolInfo where Staff.St_ID=BusCardHolder_Staff.StaffID and Location.LocationName=BusCardHolder_Staff.Location and Staff.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Staff.BusNo order by StaffName", con)
70 adp = New SqlDataAdapter(cmd)
71 ds = New DataSet()
72 adp.Fill(ds, "Staff")
73 dgw.DataSource = ds.Tables("Staff").DefaultView
74 con.Close()
75 Catch ex As Exception
76 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
77 End Try
78 End Sub
79
80 Private Sub txtStaffName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
81 Try
82 con = New SqlConnection(cs)
83 con.Open()
84 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Staff.BCH_ID) as [ID],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID], RTRIM(StaffName) as [StaffName],RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Staff.Status) as [Status] from Staff,BusCardHolder_Staff,Location,BusInfo,schoolInfo where Staff.St_ID=BusCardHolder_Staff.StaffID and Location.LocationName=BusCardHolder_Staff.Location and Staff.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Staff.BusNo and StaffName like '" & txtStaffName.Text & "%' order by StaffName", con)
85 adp = New SqlDataAdapter(cmd)
86 ds = New DataSet()
87 adp.Fill(ds, "Staff")
88 dgw.DataSource = ds.Tables("Staff").DefaultView
89 con.Close()
90 Catch ex As Exception
91 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
92 End Try
93 End Sub
94
95 Private Sub txtLocation_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtLocation.TextChanged
96 Try
97 con = New SqlConnection(cs)
98 con.Open()
99 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Staff.BCH_ID) as [ID],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID], RTRIM(StaffName) as [StaffName],RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Staff.Status) as [Status] from Staff,BusCardHolder_Staff,Location,BusInfo,schoolInfo where Staff.St_ID=BusCardHolder_Staff.StaffID and Location.LocationName=BusCardHolder_Staff.Location and Staff.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Staff.BusNo and LocationName like '" & txtLocation.Text & "%' order by StaffName", con)
100 adp = New SqlDataAdapter(cmd)
101 ds = New DataSet()
102 adp.Fill(ds, "Staff")
103 dgw.DataSource = ds.Tables("Staff").DefaultView
104 con.Close()
105 Catch ex As Exception
106 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
107 End Try
108 End Sub
109
110 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
111 Try
112 con = New SqlConnection(cs)
113 con.Open()
114 cmd = New SqlCommand("SELECT RTRIM(BusCardHolder_Staff.BCH_ID) as [ID],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID], RTRIM(StaffName) as [StaffName],RTRIM(SchoolName) as [School Name],RTRIM(BusInfo.BusNo) as [Bus No.],RTRIM(LocationName) as [Location Name], CONVERT(DateTime,JoiningDate,105) as [Joining Date],RTRIM(BusCardHolder_Staff.Status) as [Status] from Staff,BusCardHolder_Staff,Location,BusInfo,schoolInfo where Staff.St_ID=BusCardHolder_Staff.StaffID and Location.LocationName=BusCardHolder_Staff.Location and Staff.SchoolID=SchoolInfo.S_ID and BusInfo.BusNo=BusCardHolder_Staff.BusNo and JoiningDate between @d1 and @d2 order by StaffName", con)
115 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "JoiningDate").Value = dtpDateFrom.Value.Date
116 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "JoiningDate").Value = dtpDateTo.Value.Date
117 adp = New SqlDataAdapter(cmd)
118 ds = New DataSet()
119 adp.Fill(ds, "Staff")
120 dgw.DataSource = ds.Tables("Staff").DefaultView
121 con.Close()
122 Catch ex As Exception
123 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
124 End Try
125 End Sub
126 Sub Reset()
127 txtLocation.Text = ""
128 txtStaffName.Text = ""
129 GetData()
130 End Sub
131
132 Private Sub frmBusCardHolder_StaffRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
133 GetData()
134 End Sub
135
136 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
137 Reset()
138 End Sub
139
140 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
141 Me.Close()
142 End Sub
143
144 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
145 Dim rowsTotal, colsTotal As Short
146 Dim I, j, iC As Short
147 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
148 Dim xlApp As New Excel.Application
149 Try
150 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
151 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
152 xlApp.Visible = True
153
154 rowsTotal = dgw.RowCount
155 colsTotal = dgw.Columns.Count - 1
156 With excelWorksheet
157 .Cells.Select()
158 .Cells.Delete()
159 For iC = 0 To colsTotal
160 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
161 Next
162 For I = 0 To rowsTotal - 1
163 For j = 0 To colsTotal
164 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
165 Next j
166 Next I
167 .Rows("1:1").Font.FontStyle = "Bold"
168 .Rows("1:1").Font.Size = 12
169
170 .Cells.Columns.AutoFit()
171 .Cells.Select()
172 .Cells.EntireColumn.AutoFit()
173 .Cells(1, 1).Select()
174 End With
175 Catch ex As Exception
176 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
177 Finally
178 'RELEASE ALLOACTED RESOURCES
179 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
180 xlApp = Nothing
181 End Try
182 End Sub
183 End Class
